import pandas as pd
import numpy as np
from matplotlib import pyplot as plt
import matplotlib.ticker as ticker

# 设置中文显示
plt.rcParams['font.sans-serif'] = ['SimHei']

# 1. 读取数据
algorithm_chengdu = pd.read_excel('算法成都.xlsx')
data_chengdu = pd.read_excel('数据成都.xlsx')
data_chengdu = data_chengdu.drop('Unnamed: 0', axis=1)
algorithm_chengdu = algorithm_chengdu.drop('Unnamed: 0', axis=1)

# 2. 对数据和算法岗位成都地区进行学历分类统计
data_chengdu_education = data_chengdu['education_requirements'].value_counts()
data_chengdu_education.rename("数据成都", inplace=True)
algorithm_chengdu_education = algorithm_chengdu['education_requirements'].value_counts()
algorithm_chengdu_education.rename("算法成都", inplace=True)

# 3. 将两个地区的数据统计，即两个一维数据组合拼接在一起
data_new = pd.DataFrame({
    "数据成都": data_chengdu_education,
    "算法成都": algorithm_chengdu_education
})
data_new.fillna(0, inplace=True)
data_new = data_new.astype(int)

# 4. 调整行列位置，岗位要求、数据成都、算法成都为列，学历人数为行
data_new.reset_index(inplace=True)
data_new.columns = ["岗位要求", "数据成都", "算法成都"]

# 5. 调整行的顺序，学历从低到高
education_order = ["大专", "本科", "硕士", "博士", "2年经验"]
data_new = data_new.loc[data_new["岗位要求"].isin(education_order)].set_index("岗位要求").reindex(education_order).reset_index()

# 6. 显示柱状图，bar1是数据成都值，bar2是算法成都值
bar_width = 0.35
index = range(len(data_new["岗位要求"]))

fig, ax = plt.subplots()
ax.xaxis.set_major_locator(ticker.FixedLocator(index))
ax.xaxis.set_major_formatter(ticker.FixedFormatter(data_new["岗位要求"]))

bar1 = ax.bar(index, data_new['数据成都'], bar_width, label='数据成都', color='blue')
bar2 = ax.bar([i + bar_width for i in index], data_new['算法成都'], bar_width, label='算法成都', color='red')

# 7. 柱状图上数字显示
for bar in bar1:
    height = bar.get_height()
    ax.annotate('{}'.format(height), xy=(bar.get_x() + bar.get_width() / 2, height), xytext=(0, 3), textcoords="offset points", ha='center', va="bottom")
for bar in bar2:
    height = bar.get_height()
    ax.annotate('{}'.format(height), xy=(bar.get_x() + bar.get_width() / 2, height), xytext=(0, 3), textcoords="offset points", ha='center', va="bottom")

# 8. 柱状图上数字显示及其他配置
plt.title("成都地区算法和数据岗位对学历的需求")
plt.xlabel("学历", fontsize=16)
plt.ylabel("人数", fontsize=16)
ax.set_xticklabels(data_new["岗位要求"], ha='left')
ax.legend()
plt.tight_layout()
plt.savefig('./picture/5 - 4.成都地区算法和数据岗位对学历的需求.png')
plt.show()